package group32.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import group32.client.FriendService;
import group32.shared.User;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;


public class FriendServiceImpl extends RemoteServiceServlet implements FriendService{

Connection connection;
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	/**
	 * Constructor -- initialize our source tables and the object
	 */
	public FriendServiceImpl() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String serverName = "fling.seas.upenn.edu:3306";
			String database = "vdurey";
			String url = "jdbc:mysql://" + serverName + "/" + database;
			//url = "jdbc:mysql://fling.seas.upenn.edu:3306/vdurey";
			String user = "vdurey";
			String password = "cis550azerty";
			
			connection = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			//System.exit(1);
		} catch (SQLException e) {
			e.printStackTrace();
			//System.exit(1);
		}
	}
	User u=new User();
	/**
	 * Returns a relation output by computing a query
	 */
	@Override
	public ArrayList<String> getRelation(int userID) throws IllegalArgumentException {
		ArrayList<String> result = new ArrayList<String>();
		try {
               Statement statement = connection.createStatement();
			
			if (statement.execute("select U.userid ,U.firstname , U.lastname from (SELECT DISTINCT R.a AS userID, R.c as suggestedFriendID FROM ( SELECT f1.userID as a, f1.friendsID as b, f2.friendsID as c FROM friends f1, friends f2 WHERE f1.friendsID = f2.userID and f1.userId= "+ userID + ")R WHERE R.a in (SELECT f1.userID FROM friends f1, friends f2 WHERE f1.friendsID = f2.userID AND R.c = f2.friendsID AND f1.friendsID != R.b) AND (R.a, R.c) NOT IN (SELECT userID, friendsID FROM friends) AND R.a <> R.c LIMIT 2)A, users U where suggestedFriendID=U.userid;")){
				ResultSet rs = statement.getResultSet();
				 while(rs.next())
					 
					{
					int ud= rs.getInt("U.userid");	
					String fname = rs.getString("U.firstname");
					String lname = rs.getString("U.lastname");
					String name = fname +" "+ lname;
				    String nid = Integer.toString(ud);
				    result.add(name);
				    result.add(nid);
				    
					}
					return result;
			}
			}
  catch (SQLException e) 
                {
        			// TODO Auto-generated catch block
            	 System.err.println("Mysql Statement Error: " );
            	 e.printStackTrace();
            	 result=null;
        		}
                   
            return result;
			}
		
	
	
	@Override
	public void insertnewfriend(int uid,String name,int fid) throws IllegalArgumentException {
		
		try {
			Statement statement = connection.createStatement();
			int newid=0;
			
			
			statement.execute("INSERT INTO tempFriends (userID,name,friendsID) VALUES (" + uid  + ",'"+name+"',"+ newid + ")");
			
				
		} 
		
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
}
